package com.dao;
import javax.crypto.BadPaddingException;
import javax.crypto.IllegalBlockSizeException;
import javax.jws.WebService;

import java.io.UnsupportedEncodingException;
import java.security.InvalidKeyException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.*;

import com.beans.Movie;
import com.beans.Person;
import com.bussiness.PersonBussiness;
import com.constants.SQLConstants;
import com.exceptions.DeleteCustomerWithOutstandingMovieException;
import com.exceptions.DeleteUserFailedException;
import com.exceptions.DuplicateUserIdException;
import com.exceptions.InvalidCustomerNameException;
import com.exceptions.InvalidEmailException;
import com.exceptions.InvalidUserNamePasswordException;
import com.exceptions.MinimumPwdLengthException;
import com.exceptions.MovieAlreadyDeletedException;
import com.exceptions.MovieAlreadyExistsException;
import com.exceptions.MovieUnavailableException;
import com.exceptions.PasswordNotMatchingException;
import com.exceptions.ServiceUnavailableException;
import com.exceptions.UserCartIsEmptyException;
import com.exceptions.UserException;
import com.sun.org.apache.xml.internal.security.exceptions.Base64DecodingException;
import com.sun.org.apache.xml.internal.security.utils.Base64;
import com.utility.DBConnection;
//import com.utility.DBConnection_trial;
import com.validations.AllValidator;
import com.validations.Encrypt;
import com.constants.*;
import java.util.*;
import java.util.Date;

@WebService
public class PersonDAO 
{
	
	public static int SUCCESSCOUNT=2;
	/*public Person createUser(Person userData) throws DuplicateUserIdException,ServiceUnavailableException, InvalidEmailException, InvalidCustomerNameException, MinimumPwdLengthException, InvalidKeyException, BadPaddingException, IllegalBlockSizeException, Base64DecodingException
	{
		//DBConnection_trial trial = new DBConnection_trial();

		Connection con = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;
		PreparedStatement ps = null;
		PreparedStatement ps1 = null;
		PreparedStatement ps2 = null;
		PreparedStatement ps3 = null;
		PreparedStatement ps4 = null;
		PreparedStatement ps5 = null;
		int count = 0,count1 = 0;
		long membershipno = 0;
		String custtype = "P";
		Person successinsert=new Person();
		java.util.Date s1=new java.util.Date();
		
		//validation for name , email and password
		boolean emailResult,firstnameResult,lastnameResult,passwordResult;
		emailResult = AllValidator.isEmailValid(userData.getEmailId());
		if(!emailResult)
		{
			throw new InvalidEmailException();
		}
		//Decode the password to check the validation of password.
	
		passwordResult=AllValidator.validate(Encrypt.decrypt(Base64.decode(userData.getPassword())));
		if(!passwordResult)
		{
			throw new MinimumPwdLengthException();
		}	
		firstnameResult = AllValidator.namevalidate(userData.getFirstName()) ;
		if(!firstnameResult)
		{
			throw new InvalidCustomerNameException();
		}	 
		lastnameResult = AllValidator.namevalidate(userData.getLastName());
		if(!lastnameResult)
		{
			throw new InvalidCustomerNameException();
		}
		else
		{
		try
		{
		//con = DBConnection_trial.getConnection();
				con = DBConnection.getConnection();
		if(con == null)
		{
			System.out.println("Service unavailable");
			throw new ServiceUnavailableException();	
		}
		System.out.println("Inserting a new person into db on SignUp");
		String preparestmt2 = SQLConstants.checkforduplicates;
		ps2=con.prepareStatement(preparestmt2);
		ps2.setString(1, userData.getEmailId());
		System.out.println("QUERY:		" + ps2.toString());
		rs = ps2.executeQuery();
		try
		{
			if(rs.first())
			{
				System.out.println("duplicate user id exception");
				throw new DuplicateUserIdException();	
			}
			else
			{
				String movielist = null;
				 * int movielimit =0, issuedmovie =0;
				 * double rent = 0;
				 * String customertype=userData.getCustomerType();

				 * if (customertype.equals(custtype))
				{
					movielimit = 10
				}
				else
				{
					movielimit =2;
				}
				rent = 0.0;
				issuedmovie=0;
				movielist = "";
		
				String SQLQuery = "CALL videolibrary.SP_CreateUsers("
						 + "'" + userData.getFirstName() + "','" + userData.getLastName()+"','" + userData.getAddress()+"','"+userData.getCity()+"','"+ userData.getState()+"',"
						+userData.getZipCode()+",'" +userData.getEmailId() +"','"+ userData.getPassword()+ "','"+(new Timestamp(s1.getTime())).toString() +"','" +userData.getCustomerType()) + "'," 
						+ movielimit + "," +issuedmovie +"," +rent+ ",'"+ movielist+"')";

				System.out.println("Quer is" + SQLQuery);

				Statement stmt = con.createStatement();

				stmt.executeQuery(SQLQuery);
				
				//Inserting values from person bean into the customer table in db
				String prepareStmt1 = SQLConstants.createuser;
				ps = con.prepareStatement(prepareStmt1);
				ps.setString(1, userData.getFirstName());
				ps.setString(2, userData.getLastName());
				ps.setString(3, userData.getAddress());
				ps.setString(4, userData.getCity());
				ps.setString(5, userData.getState());
				ps.setInt(6, userData.getZipCode());
				ps.setString(7, userData.getEmailId());
				ps.setString(8,userData.getPassword());//encrypting and storing pwd in db
				ps.setString(9,(new Timestamp(s1.getTime())).toString());

				System.out.println("QUERY: " + ps.toString());
				count = ps.executeUpdate();
				if (count > 0) 
				{
					//if executeupdate passed, return value is 1
					System.out.println("OUTPUT:	User has been successfully entered into the customer Database"+ "\n");	
				}
				else
				{
					//if executeupdate failed return value is 0
					System.out.println("OUTPUT:	User inserting into the customer Database failed"+ "\n");
				}
				//Get the Membership Number from the customer table
				//Person person = getPersonDetail();

				String prepareStmt4 = SQLConstants.getmemshipno;
				ps5=con.prepareStatement(prepareStmt4);
				ps5.setString(1,userData.getEmailId());
				rs3= ps5.executeQuery();
				if(rs3 !=null)
				{
					rs3.beforeFirst();
					if(rs3.first())
					{
						membershipno = rs3.getLong(1);
					}
				}
				//Inserting values from customeraccount bean into the customeraccouunt table in db
				String prepareStmt2 = SQLConstants.insertuserintocustaccount;
				ps1=con.prepareStatement(prepareStmt2);
				ps1.setString(1, userData.getCustomerType());			
				ps1.setLong(2, membershipno );
				System.out.println("Customer type "+userData.getCustomerType());
				String customertype=userData.getCustomerType();
				if (customertype.equals(custtype))
				{
					ps1.setInt(3,10);
				}
				else
				{
					ps1.setInt(3, 2);
				}
				ps1.setInt(4,0);
				ps1.setInt(5, 0);
				ps1.setString(6, "");
				System.out.println("QUERY:		" + ps1.toString());
				count1 = ps1.executeUpdate();
				if (count1 > 0) 
				{
					//if executeupdate passed, return value is 1
					System.out.println("OUTPUT:	User has been successfully entered into the customeraccount Database"
							+ "\n");
				}
				else
				{
					//if executeupdate failed return value is 0
					System.out.println("OUTPUT:	User inserting into the customeraccount Database failed"
							+ "\n");
				}
			}
		}
		catch(SQLException sql)
		{
			sql.printStackTrace();
		}
		//checking if both the updates succeeded
		if(count+count1 == SUCCESSCOUNT)
		{
			//Get all the customer information form customer table and customeraccount table
			String prepareStmt3 = SQLConstants.getAllPersonData; 
			ps3=con.prepareStatement(prepareStmt3);	
			ps3.setString(1, userData.getEmailId());
			rs1=ps3.executeQuery();
			if(rs1 !=null)
			{
				rs1.last();
				rs1.beforeFirst();
				if(rs1.first())
				{
					//String login=rs1.getDate(9).toString();
					successinsert.setMemberShipNumber(rs1.getInt(1));
					successinsert.setFirstName(rs1.getString(2));
					successinsert.setLastName(rs1.getString(3));
					successinsert.setAddress(rs1.getString(4));
					successinsert.setCity(rs1.getString(5));
					successinsert.setState(rs1.getString(6));
					successinsert.setZipCode(rs1.getInt(7));
					successinsert.setEmailId(rs1.getString(8));
					successinsert.setLastLoggedIn(rs1.getString(9));
					successinsert.setCustomerType(rs1.getString(10));
					successinsert.setMovieLimit(rs1.getInt(11));
					successinsert.setIssuedMovies(rs1.getInt(12));
					successinsert.setIssuedMoviesList(rs1.getString(13));
					successinsert.setBalance(rs1.getDouble(14));				
				}
			}
		}
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		
		
	finally 
	{
		try 
		{
			if(ps!=null)
			{
			ps.close();
			}
			if(ps1!=null)
			{
			ps1.close();
			}
			if(ps2!=null)
			{
			ps2.close();
			}
			if(ps3!=null)
			{
			ps3.close();
			}
			if(ps4!=null)
			{
			ps4.close();
			}
			if(ps5!=null)
			{
			ps5.close();
			}
		} 
		catch (Exception e) 
		{
			System.out.println("error while closing prepare statement in createuser function");
		}
		try 
		{
			if(rs!=null)
			{
			rs.close();
			}
			if(rs1!=null)
			{
			rs1.close();
			}
			if(rs2!=null)
			{
			rs2.close();
			}
			if(rs3!=null)
			{
			rs3.close();
			}
		} 
		catch (Exception e) 
		{
			System.out.println("error closing result set in createuser function");
		}
		try 
		{
			if(con!=null)
			{
			con.close();
			}
		}
		catch (Exception e) 
		{
			System.out.println("error closing conenction in createuser function");
		}
	}
	return successinsert;
	}
}*/
	
	public Person createUser(Person userData) throws DuplicateUserIdException,ServiceUnavailableException, InvalidEmailException, InvalidCustomerNameException, MinimumPwdLengthException, InvalidKeyException, BadPaddingException, IllegalBlockSizeException, Base64DecodingException
	{
		DBConnection dbcon = new DBConnection();

		Connection con = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		PreparedStatement ps2 = null;
		PreparedStatement ps3 = null;
		PreparedStatement ps4 = null;
		PreparedStatement ps5 = null;
		int count = 0,count1 = 0;
		long membershipno = 0;
		String custtype = "P";
		Person successinsert=new Person();
		java.util.Date s1=new java.util.Date();
		
		//validation for name , email and password
		boolean emailResult,firstnameResult,lastnameResult,passwordResult;
		emailResult = AllValidator.isEmailValid(userData.getEmailId());
		if(!emailResult)
		{
			throw new InvalidEmailException();
		}
		//Decode the password to check the validation of password.
	
		passwordResult=AllValidator.validate(Encrypt.decrypt(Base64.decode(userData.getPassword())));
		if(!passwordResult)
		{
			throw new MinimumPwdLengthException();
		}	
		firstnameResult = AllValidator.namevalidate(userData.getFirstName()) ;
		if(!firstnameResult)
		{
			throw new InvalidCustomerNameException();
		}	 
		lastnameResult = AllValidator.namevalidate(userData.getLastName());
		if(!lastnameResult)
		{
			throw new InvalidCustomerNameException();
		}
		else
		{
		try
		{
		//con = DBConnection_trial.getConnection();
				con = DBConnection.getConnection();
		if(con == null)
		{
			System.out.println("Service unavailable");
			throw new ServiceUnavailableException();	
		}
		System.out.println("Inserting a new person into db on SignUp");
		String preparestmt2 = SQLConstants.checkforduplicates;
		ps2=con.prepareStatement(preparestmt2);
		ps2.setString(1, userData.getEmailId());
		System.out.println("QUERY:		" + ps2.toString());
		rs = ps2.executeQuery();
		try
		{
			if(rs.first())
			{
				System.out.println("duplicate user id exception");
				throw new DuplicateUserIdException();	
			}
			else
			{
				String movielist = null;
				 int movielimit =0, issuedmovie =0;
				 double rent = 0;
				 String customertype=userData.getCustomerType();

				if (customertype.equals(custtype))
				{
					movielimit = 10;
				}
				else
				{
					movielimit =2;
				}
				rent = 0.0;
				issuedmovie=0;
				movielist = "";	
				String SQLQuery = "CALL videolibrary.SP_CreateUsers("
						 + "'" + userData.getFirstName() + "','" + userData.getLastName()+"','" + userData.getAddress()+"','"+userData.getCity()+"','"+ userData.getState()+"',"
						+userData.getZipCode()+",'" +userData.getEmailId() +"','"+ userData.getPassword()+ "','"+(new Timestamp(s1.getTime())).toString() +"','" +userData.getCustomerType()+ "'," 
						+ movielimit + "," +issuedmovie +"," +rent+ ",'"+ movielist+"')";
				System.out.println("Quer is" + SQLQuery);
				Statement stmt = con.createStatement();
				stmt.executeQuery(SQLQuery);
				
			String prepareStmt3 = SQLConstants.getAllPersonData; 
			ps3=con.prepareStatement(prepareStmt3);	
			ps3.setString(1, userData.getEmailId());
			rs1=ps3.executeQuery();
			if(rs1 !=null)
			{
				rs1.last();
				rs1.beforeFirst();
				if(rs1.first())
				{
					successinsert.setMemberShipNumber(rs1.getInt(1));
					successinsert.setFirstName(rs1.getString(2));
					successinsert.setLastName(rs1.getString(3));
					successinsert.setAddress(rs1.getString(4));
					successinsert.setCity(rs1.getString(5));
					successinsert.setState(rs1.getString(6));
					successinsert.setZipCode(rs1.getInt(7));
					successinsert.setEmailId(rs1.getString(8));
					successinsert.setLastLoggedIn(rs1.getString(9));
					successinsert.setCustomerType(rs1.getString(10));
					successinsert.setMovieLimit(rs1.getInt(11));
					successinsert.setIssuedMovies(rs1.getInt(12));
					successinsert.setIssuedMoviesList(rs1.getString(13));
					successinsert.setBalance(rs1.getDouble(14));				
				}
			}	
			}
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		
		
	finally 
	{
		try 
		{
			if(ps2!=null)
			{
			ps2.close();
			}
			if(ps3!=null)
			{
			ps3.close();
			}
			if(ps4!=null)
			{
			ps4.close();
			}
			if(ps5!=null)
			{
			ps5.close();
			}
		} 
		catch (Exception e) 
		{
			System.out.println("error while closing prepare statement in createuser function");
		}
		try 
		{
			if(rs!=null)
			{
			rs.close();
			}
			if(rs1!=null)
			{
			rs1.close();
			}
		} 
		catch (Exception e) 
		{
			System.out.println("error closing result set in createuser function");
		}
		try 
		{
			if(con!=null)
			{
			con.close();
			}
		}
		catch (Exception e) 
		{
			System.out.println("error closing conenction in createuser function");
		}
	}
}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
	}
		return successinsert;

	}
		
	public Person getAllPersonDetail(String emailId)
	{
		Connection con=null;
		ResultSet rs=null;
		PreparedStatement prest =null;
		Person personList=null;
		try{
			//con=DBConnection_trial.getConnection();
					con = DBConnection.getConnection();
			System.out.println("Creating DB Connection");
			String prepStatement=SQLConstants.getAllPersonData;
			prest = con.prepareStatement(prepStatement);
			prest.setString(1, emailId);
			
			rs = prest.executeQuery();
			if (rs != null) {
				rs.last();
				rs.beforeFirst();
				if (rs.first()) 
				{
					personList = new Person();
					personList.setMemberShipNumber(rs.getLong(1));
					personList.setFirstName(rs.getString(2));
					personList.setLastName(rs.getString(3));
					personList.setAddress(rs.getString(4));
					personList.setCity(rs.getString(5));
					personList.setState(rs.getString(6));
					personList.setZipCode(rs.getInt(7));
					personList.setEmailId(rs.getString(8));
					personList.setLastLoggedIn(rs.getString(9));
					personList.setCustomerType(rs.getString(10));
					personList.setMovieLimit(rs.getInt(11));
					personList.setIssuedMovies(rs.getInt(12));
					personList.setIssuedMoviesList(rs.getString(13));
					personList.setBalance(rs.getDouble(14));
				}
			}		

		}
		catch(Exception e)
		{
			System.err.println(e);
		}
		finally 
        {
			try 
			{
				prest.close();
			} 
			catch (Exception e) 
			{
				System.err.println(e);
			}
			try 
			{
				con.close();
			} 
			catch (Exception e) 
			{
				System.err.println(e);
			}
		}
		return personList;
	}
    
	public Person validateUser(Person user) throws InvalidUserNamePasswordException,ServiceUnavailableException,PasswordNotMatchingException, InvalidKeyException, BadPaddingException, IllegalBlockSizeException, UnsupportedEncodingException, InvalidEmailException, MinimumPwdLengthException, Base64DecodingException
	{
		DBConnection dbcon = new DBConnection();
		Person person = new Person();
		Connection con = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		PreparedStatement ps1 = null;
		java.util.Date s1=new java.util.Date();	
		int count = 0;
		boolean emailResult,passwordResult;
		emailResult = AllValidator.isEmailValid(user.getEmailId());
		if(!emailResult)
		{
			throw new InvalidEmailException();
		}
		passwordResult=AllValidator.validate(Encrypt.decrypt(Base64.decode(user.getPassword())));
		if(!passwordResult)
		{
			throw new MinimumPwdLengthException();
		}	
		else
		{
		try
		{
			//con = DBConnection_trial.getConnection();
					con = DBConnection.getConnection();
			
			if(con == null)
			{
				throw new ServiceUnavailableException();
			}
			String preparestmt = SQLConstants.validateuser;
			ps=con.prepareStatement(preparestmt);
			ps.setString(1, user.getEmailId());
			rs = ps.executeQuery();
			if(rs.first())
			{
				String password;
				try
				{
					password = Encrypt.decrypt(Base64.decode(rs.getString(1)));
					String actualpassword;
					try 
					{
						actualpassword = Encrypt.decrypt(Base64.decode(user.getPassword()));
						if(password.equals(actualpassword))
						{
							System.out.println("Authentication success");
							person = getAllPersonDetail(user.getEmailId()); 
							String preparestmt1=SQLConstants.updatelastlogin;
							ps1=con.prepareStatement(preparestmt1);
							ps1.setString(1, new Timestamp(s1.getTime()).toString());
							ps1.setString(2, user.getEmailId());
							count = ps1.executeUpdate();
							if (count == 1)
							{
								System.out.println("The Last login for user has been updated to current date time");
							}
							else
							{
								System.out.println("The Last login update for user failed");
							}
						}
						else
						{
							System.out.println("The passwords dont match");
							throw new PasswordNotMatchingException();
						}
					} 
					catch (Base64DecodingException e) 
					{
						System.out.println("The passwords could not be encrypted");
					}
				} 
				catch (Base64DecodingException e1) {
					System.out.println("The passwords could not be encrypted");
				}
				
			}
			else
			{
				System.out.println("Not a valid userid");
				throw new InvalidUserNamePasswordException();
			}
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
	finally 
	{
		try 
		{
			if(ps!=null)
			{
			ps.close();
			}
			if(ps1!=null)
			{
			ps1.close();
			}
			if(rs!=null)
			{
			rs.close();
			}
		} 
		catch (Exception e) 
		{
			System.out.println("error while closing prepare statement in validateuser function");
		}
		try 
		{
			if(con!=null)
			{
			con.close();
			}
		}
		catch (Exception e) 
		{
			System.out.println("error while closing conenction in validateuser function");
		}
	}
		return person;
	}
	}
	public Person updateUser(Person userData) throws ServiceUnavailableException, MinimumPwdLengthException, InvalidCustomerNameException, InvalidKeyException, BadPaddingException, IllegalBlockSizeException, Base64DecodingException
	{
		Connection con = null;
		PreparedStatement ps = null;
		int count = 0;
		Person personData = null;
		boolean emailResult,firstnameResult,lastnameResult,passwordResult;
		//Decode the password to check the validation of password.
		passwordResult=AllValidator.validate(Encrypt.decrypt(Base64.decode(userData.getPassword())));
		if(!passwordResult)
		{
			throw new MinimumPwdLengthException();
		}	
		firstnameResult = AllValidator.namevalidate(userData.getFirstName()) ;
		if(!firstnameResult)
		{
			throw new InvalidCustomerNameException();
		}	 
		lastnameResult = AllValidator.namevalidate(userData.getLastName());
		if(!lastnameResult)
		{
			throw new InvalidCustomerNameException();
		}
		else
		{
		try
		{
			//con = DBConnection_trial.getConnection();
					con = DBConnection.getConnection();
			
			if(con == null)
			{
				throw new ServiceUnavailableException();
			}
			String preparestmt = SQLConstants.updateAllUserData;
			ps=con.prepareStatement(preparestmt);
			ps.setString(1, userData.getFirstName());
			ps.setString(2, userData.getLastName());
			ps.setString(3, userData.getAddress());
			ps.setString(4, userData.getCity());
			ps.setString(5, userData.getState());
			ps.setInt(6, userData.getZipCode());
			ps.setString(7, userData.getCustomerType());
			ps.setString(8, userData.getPassword());
			ps.setString(9, userData.getEmailId());
			count = ps.executeUpdate();
			
			//get all data
			personData = getAllPersonDetail(userData.getEmailId());
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		finally 
		{
			try 
			{
				if(ps!=null)
				{
				ps.close();
				}
			} 
			catch (Exception e) 
			{
			System.out.println("error while closing prepare statement in validateuser function");
			}
			try 
			{
				if(con!=null)
				{
				con.close();
				}
			}
			catch (Exception e) 
			{
			System.out.println("error while closing conenction in validateuser function");
			}
		}
		return personData;
	}
	}
	public int removeUser(String emailId) throws ServiceUnavailableException,DeleteCustomerWithOutstandingMovieException, UserException, InvalidEmailException, DeleteUserFailedException
	{	
		Connection con = null;
		ResultSet rs = null;
		ResultSet rs1= null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;
		PreparedStatement ps = null;
		PreparedStatement ps1 = null;
		PreparedStatement ps2 = null;
		PreparedStatement ps3 = null;
		PreparedStatement ps4 = null;
		PreparedStatement ps5 = null;
		Savepoint three = null,four = null;
		int count2=0,count1=0;
		long membershipno=0;
		boolean emailResult = AllValidator.isEmailValid(emailId);
		if(!emailResult)
		{
			throw new InvalidEmailException();
		}
		else
		{
		try
		{
			//con = DBConnection_trial.getConnection();
					con = DBConnection.getConnection();
			
			if(con == null)
			{
				throw new ServiceUnavailableException();
			}
			String preparestmt = SQLConstants.selectusertodelete;
			ps=con.prepareStatement(preparestmt);
			ps.setString(1, emailId);
			rs=ps.executeQuery();
			if(rs.first())
			{
				membershipno=rs.getLong(1);
			}
			else
			{
				throw new UserException();
			}
			String preparestmt1=SQLConstants.checkforoutstandingmovies;
			ps1=con.prepareStatement(preparestmt1);
			ps1.setLong(1, membershipno);
			rs1=ps1.executeQuery();
			if (rs1 != null) 
			{
				rs1.last();
				rs1.beforeFirst();

				while (rs1.next()) 
				{
					if(rs1.getInt(1) > 0)
					{
						throw new DeleteCustomerWithOutstandingMovieException();
					}
					else
					{
						try
						{
							
						String preparestmt3 = SQLConstants.checkordertablebeforedelete;
						ps2 = con.prepareStatement(preparestmt3);
						ps2.setLong(1, membershipno);
						rs3 = ps2.executeQuery();
						if(rs3.first())
						{
							throw new DeleteCustomerWithOutstandingMovieException();
						}
						else
						{
							String preparestmt4 = SQLConstants.deleteuserfromorder;
							ps3 = con.prepareStatement(preparestmt4);
							ps3.setLong(1, membershipno);
							count1=ps3.executeUpdate();
						}
						String preparestmt6 = SQLConstants.verifyinqueuebeforedelete;
						ps4 = con.prepareStatement(preparestmt6);
						ps4.setLong(1, membershipno);
						rs2=ps4.executeQuery();
						if(rs2 != null)
						{
						String preparestmt5 = SQLConstants.deleteuserfromqueue;
						ps5 = con.prepareStatement(preparestmt5);
						ps5.setLong(1, membershipno);
						count2=ps5.executeUpdate();
						three = con.setSavepoint("deletefromqueue");
						}
						
						String SQLQuery = "CALL videolibrary.SP_DeleteUser('"+ emailId +"'," + membershipno+")";
						System.out.println("Quer is" + SQLQuery);
						Statement stmt = con.createStatement();
						stmt.executeQuery(SQLQuery);
						four = con.setSavepoint();
						
						return 1;
						}
						catch(SQLException e)
						{
						System.out.println("Exception while deleting user");
						con.rollback(three);
						con.rollback(four);
						return 0;
						
						}
					}	
				}
			}
					
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally 
		{
			try 
			{
				if(ps!=null)
				{
				ps.close();
				}
				if(ps1!=null)
				{
				ps1.close();
				}
				if(ps2!=null)
				{
				ps2.close();
				}
				if(ps3!=null)
				{
				ps3.close();
				}
				if(ps4!=null)
				{
				ps4.close();
				}
				if(ps5!=null)
				{
				ps5.close();
				}

			} 
			catch (Exception e) 
			{
			System.out.println("error while closing prepare statement in removeUser function");
			}
			try 
			{
				if(rs!=null)
				{
				rs.close();
				}
				if(rs1!=null)
				{
				rs1.close();
				}
				if(rs2!=null)
				{
				rs2.close();
				}
			} 
			catch (Exception e) 
			{
			System.out.println("error while closing prepare statement in removeUser function");
			}
			try 
			{
				if(con!=null)
				{
				con.close();
				}			
			}
			catch (Exception e) 
			{
			System.out.println("error while closing conenction in removeUser function");
			}
		}
		return 0;
		//return 1 on success and 0 on failure
	}
}

	public Movie[] customerorder(long memshipid) throws ServiceUnavailableException
	{
		Connection con = null;
		PreparedStatement ps = null;
		PreparedStatement ps1 = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		int size=0;
		Movie[] movie=null;
		int movieid=0;
		int currRow=0;
		try
		{
			//con = DBConnection_trial.getConnection();
					con = DBConnection.getConnection();
			
			if(con == null)
			{
				throw new ServiceUnavailableException();
			}
			String preparestmt = SQLConstants.customerorder;
			ps=con.prepareStatement(preparestmt);
			ps.setLong(1, memshipid);
			rs=ps.executeQuery();
			if (rs !=null)
			{
				rs.last();
				size=rs.getRow();
				movie=new Movie[size];
				rs.beforeFirst();
			}
			while(rs.next())
			{
				movieid=rs.getInt(1);
				String preparestmt1=SQLConstants.customermoviedetails;
				ps1=con.prepareStatement(preparestmt1);
				ps1.setInt(1, movieid);
				rs1=ps1.executeQuery();
				if(rs1 !=null)
				{
				rs1.beforeFirst();
				}
				if(rs1.first())
				{
				Movie order=new Movie();
				order.setMovieName(rs1.getString(1));
				order.setRating(rs1.getDouble(3));
				order.setRentdate(rs.getString(2));
				order.setRentamount(rs.getInt(3));
				order.setMovieBanner(rs1.getString(4));
				order.setCategoryname(rs1.getString(5));
				movie[currRow]=order;
				currRow++;
				}
			}
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		finally 
		{
			try 
			{
				if(ps!=null)
				{
				ps.close();
				}
				if(ps1!=null)
				{
				ps1.close();
				}
			} 
			catch (Exception e) 
			{
			System.out.println("error closing prepare statement in customerorder function");
			}
			try 
			{
				if(rs!=null)
				{
				rs.close();
				}
				if(rs1!=null)
				{
				rs1.close();
				}
			} 
			catch (Exception e) 
			{
			System.out.println("error closing resultset in customerorder function");
			}
			try 
			{
				if(con!=null)
				{
				con.close();
				}			}
			catch (Exception e) 
			{
			System.out.println("error closing conenction in customerorder function");
			}
		}
	return movie;
	}
	public Movie[] customerqueue(long memshipid) throws ServiceUnavailableException, UserCartIsEmptyException
	{
		Connection con = null;
		PreparedStatement ps = null;
		PreparedStatement ps1 = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		int size=0;
		Movie[] movie=null;
		int movieid=0;
		int currRow=0;
		try
		{
			//con = DBConnection_trial.getConnection();
					con = DBConnection.getConnection();
			
			if(con == null)
			{
				throw new ServiceUnavailableException();
			}
			String preparestmt = SQLConstants.customerqueue;
			ps=con.prepareStatement(preparestmt);
			ps.setLong(1, memshipid);
			rs=ps.executeQuery();
			if (rs !=null)
			{
				rs.last();
				size=rs.getRow();
				movie=new Movie[size];
				rs.beforeFirst();	
			}
			else
			{
				System.out.println("user cart in empty");
				throw new UserCartIsEmptyException();
			}
			while(rs.next())
			{
				movieid=rs.getInt(1);
				String preparestmt1=SQLConstants.customermoviedetails;
				ps1=con.prepareStatement(preparestmt1);
				ps1.setInt(1, movieid);
				rs1=ps1.executeQuery();
				if(rs1 !=null)
				{
				rs1.beforeFirst();
				}
				if(rs1.first())
				{
				Movie order=new Movie();
				order.setMovieId(movieid);
				order.setMovieName(rs1.getString(1));
				order.setRating(rs1.getDouble(3));
				order.setMovieBanner(rs1.getString(4));
				order.setCategoryname(rs1.getString(5));
				order.setRent(rs1.getDouble(6));
				movie[currRow]=order;		
				currRow++;
				}
			}
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		finally 
		{
			try 
			{
				if(ps!=null)
				{
				ps.close();
				}
				if(ps1!=null)
				{
				ps1.close();
				}
			} 
			catch (Exception e) 
			{
			System.out.println("error closing prepare statement in customerqueue function");
			}
			try 
			{
				if(rs!=null)
				{
				rs.close();
				}
				if(rs1!=null)
				{
				rs1.close();
				}
			} 
			catch (Exception e) 
			{
			System.out.println("error closing resultset in customerqueue function");
			}
			try 
			{
				if(con!=null)
				{
				con.close();
				}			
			}
			catch (Exception e) 
			{
			System.out.println("error closing conenction in customerqueue function");
			}
		}
	return movie;
	}
	public int addtoQueue(int movieid,long memshipid) throws MovieAlreadyExistsException,ServiceUnavailableException
	{
		Connection con = null;
		PreparedStatement ps = null;
		PreparedStatement ps1 = null;
		ResultSet rs = null;
		int success=0;
		int insertsuccess=0;
		try
		{
			//con = DBConnection_trial.getConnection();
					con = DBConnection.getConnection();
			
			if(con == null)
			{
				throw new ServiceUnavailableException();
			}
			String preparestmt = SQLConstants.beforeinserttoqueue;
			ps=con.prepareStatement(preparestmt);
			ps.setLong(1, memshipid);
			ps.setInt(2, movieid);
			rs=ps.executeQuery();
			if (rs.first())
			{	
				throw new MovieAlreadyExistsException();
			}
			else 
			{
					String preparestmt1=SQLConstants.inserttoQueue;
					ps1=con.prepareStatement(preparestmt1);
					ps1.setLong(1,memshipid);
					ps1.setInt(2, movieid);
					insertsuccess =  ps1.executeUpdate();
					if(insertsuccess == 0)
					{
						throw new ServiceUnavailableException();
					}
			}
			
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		finally 
		{
			try 
			{
				if(ps!=null)
				{
					ps.close();
				}
				if(ps1!=null)
				{
					ps1.close();
				}
			} 
			catch (Exception e) 
			{
				System.out.println("error closing prepare statement in addtoqueue function");
			}
			try 
			{
				if(rs!=null)
				{
					rs.close();
				}
			} 
			catch (Exception e) 
			{
				System.out.println("error closing resultset in addtoqueue function");
			}
			try 
			{
				if(con!=null)
				{
					con.close();
				}			
			}
			catch (Exception e) 
			{
				System.out.println("error closing conenction in addtoqueue function");
			}
		}

		return insertsuccess;
		// if 1 then success delete else if 0 failure delete

	}
	public int removefromQueue(int movieid,long userid) throws ServiceUnavailableException, MovieAlreadyDeletedException
	{
		Connection con = null;
		PreparedStatement ps = null;
		PreparedStatement ps1 = null;
		ResultSet rs= null;
		int success=0;

		try
		{
			//con = DBConnection_trial.getConnection();
					con = DBConnection.getConnection();
			
			if(con == null)
			{
				throw new ServiceUnavailableException();
			}
			String preparestmt1 = SQLConstants.beforedeletefromQueue;
			ps1=con.prepareStatement(preparestmt1);
			ps1.setLong(1, userid);
			ps1.setInt(2, movieid);
			rs = ps1.executeQuery();
			if(rs == null)
			throw new MovieAlreadyDeletedException();
			else
			{
			String preparestmt = SQLConstants.deletefromQueue;
			ps=con.prepareStatement(preparestmt);
			ps.setLong(1, userid);
			ps.setInt(2, movieid);
			success = ps.executeUpdate();	
			}
			
		}
		catch(SQLException sql)
		{
			sql.printStackTrace();
		}
		finally 
		{
			try 
			{
				if(ps!=null)
				{
					ps.close();
				}
				if(ps1!=null)
				{
					ps1.close();
				}
			} 
			catch (Exception e) 
			{
				System.out.println("error closing prepare statement in deletefromqueue function");
			}
			try 
			{
				if(rs!=null)
				{
					rs.close();
				}
			} 
			catch (Exception e) 
			{
				System.out.println("error closing result set in deletefromqueue function");
			}
			try 
			{
				if(con!=null)
				{
					con.close();
				}			
			}
			catch (Exception e) 
			{
				System.out.println("error closing conenction in deletefromqueue function");
			}
		}
		return success;
		// if 1 then success delete else if 0 failure delete
	}
    
    
	public Person[] getAllStates() throws ServiceUnavailableException,Exception
		{
			int size=0;
			Person[] statlist=new Person[59];
			Connection con = null;
			PreparedStatement prestmt = null;
			ResultSet rs = null;
			try {
				con=DBConnection.getConnection();
				String pstatement=SQLConstants.GETALLSTATES;
				prestmt=con.prepareStatement(pstatement);
				System.out.println("Query framed: "+prestmt.toString());
				rs=prestmt.executeQuery();
				int i=0;
				rs.last();
				size=rs.getRow();
				rs.beforeFirst();
				rs.next();
				
				
				while(rs.next())
				{
				Person person = new Person();
				person.setGetallstates(rs.getString(1));
				statlist[i]=person;
				i++;
				rs.next();
				}
				
				
				//Close all the open connections
				rs.close();
				prestmt.close();
				con.close();
			}
			catch(SQLException e){
				e.printStackTrace();
				throw new ServiceUnavailableException();
			} catch (Exception e) {
				e.printStackTrace();
				throw new ServiceUnavailableException();
			}		
			finally{
				try{
					rs.close();
				}catch(Exception e){
					
				}
				try{
					prestmt.close();
				}catch(Exception e){
					
				}
				try{
					con.close();
				}catch(Exception e){
					
				}
			}
				
		return statlist;
		}
}


